/* lamp的数据结构(PostgreSQL)
CREATE TABLE lamp (
 model TEXT PRIMARY KEY,--型号
 name_zh TEXT,--中文名
 name_en TEXT,--英文名
 amazon_id TEXT,--亚马逊ID
 tmall_id TEXT,--天猫ID
 abb_zh TEXT,--中文简称
 abb_en TEXT,--英文简称
 inch NUMERIC,--英寸
 length NUMERIC,--长
 width NUMERIC,--宽
 height NUMERIC,--高
 type INTEGER CHECK (type IN (1, 2, 3, 4, 5, 6)),--分类:1.台灯 2.落地灯 3.吊灯 4.壁灯 5.小夜灯 6.其它
 pc NUMERIC,--成本
 price_zh NUMERIC,--国内价
 price_en NUMERIC,--国外价
 mt INTEGER CHECK (mt IN (1, 2, 3, 4, 5, 6)),--底座材质:1.树脂 2.铸铁 3.合金 4.全铜 5.木 6.无底座
 main_images TEXT[],--主图数组
 detail_images TEXT[],--细节图数组
 tiktok_id TEXT,--tiktokID
 douyin_id TEXT,--抖音ID
 is_live BOOLEAN DEFAULT false,--是否已上架
 created_at TIMESTAMP DEFAULT NOW() -- 创建时间
);
*/

const Router = require("@koa/router");
const { Pool } = require("pg");
const path = require('path');
require('dotenv').config({ path: path.resolve(__dirname, '../.env') });
const router = new Router({ prefix: "/lamp" });

// 配置数据库连接池
const pool = new Pool({
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_DATABASE,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT,
});

// 新增灯具
router.post("/", async (ctx) => {
  try {
    const {
      model, name_zh, name_en, amazon_id, tmall_id, abb_zh, abb_en,
      inch, length, width, height, type, pc, price_zh, price_en,
      mt, main_images, detail_images, tiktok_id, douyin_id, is_live
    } = ctx.request.body;
    
    // 验证必填字段
    if (!model) {
      ctx.status = 400;
      ctx.body = {
        success: false,
        message: '型号(model)是必填字段'
      };
      return;
    }
    
    const result = await pool.query(
      `INSERT INTO lamp (
        model, name_zh, name_en, amazon_id, tmall_id, abb_zh, abb_en,
        inch, length, width, height, type, pc, price_zh, price_en,
        mt, main_images, detail_images, tiktok_id, douyin_id, is_live
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21)
      RETURNING *`,
      [
        model, name_zh, name_en, amazon_id, tmall_id, abb_zh, abb_en,
        inch, length, width, height, type, pc, price_zh, price_en,
        mt, main_images, detail_images, tiktok_id, douyin_id, is_live
      ]
    );
    
    ctx.status = 201;
    ctx.body = {
      success: true,
      data: result.rows[0],
      message: '灯具创建成功'
    };
  } catch (error) {
    if (error.code === '23505') { // 唯一约束违反错误
      ctx.status = 409;
      ctx.body = {
        success: false,
        message: '灯具型号已存在'
      };
    } else {
      ctx.status = 500;
      ctx.body = {
        success: false,
        message: '创建灯具失败',
        error: error.message
      };
    }
  }
});

// 获取灯具列表（支持分页）
router.get("/", async (ctx) => {
  try {
    const { page = 1, pageSize = 10, search, type, is_live } = ctx.query;
    const offset = (page - 1) * pageSize;
    
    let query = 'SELECT * FROM lamp';
    let countQuery = 'SELECT COUNT(*) FROM lamp';
    let queryParams = [];
    let countParams = [];
    let whereConditions = [];
    
    if (search) {
      whereConditions.push('(model ILIKE $' + (whereConditions.length + 1) +
                          ' OR name_zh ILIKE $' + (whereConditions.length + 1) +
                          ' OR name_en ILIKE $' + (whereConditions.length + 1) +
                          ' OR abb_zh ILIKE $' + (whereConditions.length + 1) +
                          ' OR abb_en ILIKE $' + (whereConditions.length + 1) + ')');
      queryParams.push(`%${search}%`);
      countParams.push(`%${search}%`);
    }
    
    if (type) {
      whereConditions.push('type = $' + (whereConditions.length + 1));
      queryParams.push(type);
      countParams.push(type);
    }
    
    if (is_live !== undefined) {
      whereConditions.push('is_live = $' + (whereConditions.length + 1));
      queryParams.push(is_live === 'true');
      countParams.push(is_live === 'true');
    }
    
    if (whereConditions.length > 0) {
      query += ' WHERE ' + whereConditions.join(' AND ');
      countQuery += ' WHERE ' + whereConditions.join(' AND ');
    }
    
    query += ' ORDER BY created_at DESC LIMIT $' + (queryParams.length + 1) + ' OFFSET $' + (queryParams.length + 2);
    queryParams.push(pageSize, offset);
    
    const result = await pool.query(query, queryParams);
    
    const countResult = await pool.query(countQuery, countParams);
    const total = parseInt(countResult.rows[0].count);
    
    ctx.body = {
      success: true,
      data: {
        list: result.rows,
        pagination: {
          page: parseInt(page),
          pageSize: parseInt(pageSize),
          total,
          totalPages: Math.ceil(total / pageSize)
        }
      }
    };
  } catch (error) {
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: '获取灯具列表失败',
      error: error.message
    };
  }
});

// 根据model获取灯具详情
router.get("/:model", async (ctx) => {
  try {
    const { model } = ctx.params;
    const result = await pool.query(
      'SELECT * FROM lamp WHERE model = $1',
      [model]
    );
    
    if (result.rows.length === 0) {
      ctx.status = 404;
      ctx.body = {
        success: false,
        message: '灯具不存在'
      };
      return;
    }
    
    ctx.body = {
      success: true,
      data: result.rows[0]
    };
  } catch (error) {
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: '获取灯具详情失败',
      error: error.message
    };
  }
});

// 更新灯具
router.put("/:model", async (ctx) => {
  try {
    const { model } = ctx.params;
    const {
      name_zh, name_en, amazon_id, tmall_id, abb_zh, abb_en,
      inch, length, width, height, type, pc, price_zh, price_en,
      mt, main_images, detail_images, tiktok_id, douyin_id, is_live
    } = ctx.request.body;
    
    const result = await pool.query(
      `UPDATE lamp SET
        name_zh = COALESCE($1, name_zh),
        name_en = COALESCE($2, name_en),
        amazon_id = COALESCE($3, amazon_id),
        tmall_id = COALESCE($4, tmall_id),
        abb_zh = COALESCE($5, abb_zh),
        abb_en = COALESCE($6, abb_en),
        inch = COALESCE($7, inch),
        length = COALESCE($8, length),
        width = COALESCE($9, width),
        height = COALESCE($10, height),
        type = COALESCE($11, type),
        pc = COALESCE($12, pc),
        price_zh = COALESCE($13, price_zh),
        price_en = COALESCE($14, price_en),
        mt = COALESCE($15, mt),
        main_images = COALESCE($16, main_images),
        detail_images = COALESCE($17, detail_images),
        tiktok_id = COALESCE($18, tiktok_id),
        douyin_id = COALESCE($19, douyin_id),
        is_live = COALESCE($20, is_live)
      WHERE model = $21
      RETURNING *`,
      [
        name_zh, name_en, amazon_id, tmall_id, abb_zh, abb_en,
        inch, length, width, height, type, pc, price_zh, price_en,
        mt, main_images, detail_images, tiktok_id, douyin_id, is_live, model
      ]
    );
    
    if (result.rows.length === 0) {
      ctx.status = 404;
      ctx.body = {
        success: false,
        message: '灯具不存在'
      };
      return;
    }
    
    ctx.body = {
      success: true,
      data: result.rows[0],
      message: '灯具更新成功'
    };
  } catch (error) {
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: '更新灯具失败',
      error: error.message
    };
  }
});

// 删除灯具
router.delete("/:model", async (ctx) => {
  try {
    const { model } = ctx.params;
    const result = await pool.query(
      'DELETE FROM lamp WHERE model = $1 RETURNING *',
      [model]
    );
    
    if (result.rows.length === 0) {
      ctx.status = 404;
      ctx.body = {
        success: false,
        message: '灯具不存在'
      };
      return;
    }
    
    ctx.body = {
      success: true,
      message: '灯具删除成功',
      data: result.rows[0]
    };
  } catch (error) {
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: '删除灯具失败',
      error: error.message
    };
  }
});

// 更新灯具上下架状态
router.patch("/:model/live", async (ctx) => {
  try {
    const { model } = ctx.params;
    const { is_live } = ctx.request.body;
    
    // 验证 is_live 字段
    if (typeof is_live !== 'boolean') {
      ctx.status = 400;
      ctx.body = {
        success: false,
        message: 'is_live 必须是布尔类型'
      };
      return;
    }
    
    const result = await pool.query(
      `UPDATE lamp SET
        is_live = $1
      WHERE model = $2
      RETURNING *`,
      [is_live, model]
    );
    
    if (result.rows.length === 0) {
      ctx.status = 404;
      ctx.body = {
        success: false,
        message: '灯具不存在'
      };
      return;
    }
    
    ctx.body = {
      success: true,
      data: result.rows[0],
      message: is_live ? '灯具上架成功' : '灯具下架成功'
    };
  } catch (error) {
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: '更新灯具上下架状态失败',
      error: error.message
    };
  }
});

// 更新灯具创建时间
router.patch("/:model/created-at", async (ctx) => {
  try {
    const { model } = ctx.params;
    
    const result = await pool.query(
      `UPDATE lamp SET
        created_at = NOW()
      WHERE model = $1
      RETURNING *`,
      [model]
    );
    
    if (result.rows.length === 0) {
      ctx.status = 404;
      ctx.body = {
        success: false,
        message: '灯具不存在'
      };
      return;
    }
    
    ctx.body = {
      success: true,
      data: result.rows[0],
      message: '灯具创建时间更新成功'
    };
  } catch (error) {
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: '更新灯具创建时间失败',
      error: error.message
    };
  }
});

module.exports = router;